Rendering to Excel

Microsoft Excel is one of the formats to which you can render your report using ExcelRenderingExtension. You can export excel files in two formats, i.e. Xls and Xlsx.

In order to render your report to Excel, add reference to the following assemblies in the project:

GrapeCity.ActiveReports.Export.Excel.v9.dll
GrapeCity.ActiveReports.Extensibility.v9.dll

The following steps provide an example of rendering a report in the Microsoft Excel format.

In Visual Studio, create a new WindowsForms application or open an existing one.
On the Form.cs or Form.vb that opens, double-click the title bar to create the Form_Load event.

Add the following code inside the Form_Load event.

Visual Basic.NET code. Paste INSIDE the Form Load event.	Copy Code
' Provide the page report you want to render. Dim report As New GrapeCity.ActiveReports.PageReport(New System.IO.FileInfo("C:\Sample_PageReport.rdlx")) Dim reportDocument As New GrapeCity.ActiveReports.Document.PageDocument(report) ' Create an output directory Dim outputDirectory As New System.IO.DirectoryInfo("C:\MyExcel") outputDirectory.Create() ' Provide settings for your rendering output. Dim excelSetting As New GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtensionSettings() excelSetting.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.FileFormat.Xls Dim setting As GrapeCity.ActiveReports.Extensibility.Rendering.ISettings = excelSetting ' Set the rendering extension and render the report. Dim excelRenderingExtension As New GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtension() Dim outputProvider As New GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name)) reportDocument.Render(excelRenderingExtension, outputProvider, excelSetting.GetSettings())

Visual Basic.NET code. Paste INSIDE the Form Load event.

Copy Code

' Provide the page report you want to render.
Dim report As New GrapeCity.ActiveReports.PageReport(New System.IO.FileInfo("C:\Sample_PageReport.rdlx"))
Dim reportDocument As New GrapeCity.ActiveReports.Document.PageDocument(report)

' Create an output directory
Dim outputDirectory As New System.IO.DirectoryInfo("C:\MyExcel")
outputDirectory.Create()

' Provide settings for your rendering output.
Dim excelSetting As New GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtensionSettings()
excelSetting.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.FileFormat.Xls
Dim setting As GrapeCity.ActiveReports.Extensibility.Rendering.ISettings = excelSetting

' Set the rendering extension and render the report.
Dim excelRenderingExtension As New GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtension()
Dim outputProvider As New GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name))
reportDocument.Render(excelRenderingExtension, outputProvider, excelSetting.GetSettings())

C# code. Paste INSIDE the Form Load event.	Copy Code
// Provide the page report you want to render. GrapeCity.ActiveReports.PageReport report = new GrapeCity.ActiveReports.PageReport(new System.IO.FileInfo(@"C:\Sample_PageReport.rdlx")); GrapeCity.ActiveReports.Document.PageDocument reportDocument = new GrapeCity.ActiveReports.Document.PageDocument(report); // Create an output directory System.IO.DirectoryInfo outputDirectory = new System.IO.DirectoryInfo(@"C:\MyExcel"); outputDirectory.Create(); // Provide settings for your rendering output. GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtensionSettings excelSetting = new GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtensionSettings(); excelSetting.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.FileFormat.Xls; GrapeCity.ActiveReports.Extensibility.Rendering.ISettings setting = excelSetting; //Set the rendering extension and render the report. GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtension excelRenderingExtension = new GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtension(); GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider outputProvider = new GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name)); reportDocument.Render(excelRenderingExtension, outputProvider, excelSetting.GetSettings());

C# code. Paste INSIDE the Form Load event.

Copy Code

// Provide the page report you want to render.
GrapeCity.ActiveReports.PageReport report = new GrapeCity.ActiveReports.PageReport(new System.IO.FileInfo(@"C:\Sample_PageReport.rdlx"));
GrapeCity.ActiveReports.Document.PageDocument reportDocument = new GrapeCity.ActiveReports.Document.PageDocument(report);

// Create an output directory
System.IO.DirectoryInfo outputDirectory = new System.IO.DirectoryInfo(@"C:\MyExcel");
outputDirectory.Create();

// Provide settings for your rendering output.
GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtensionSettings excelSetting = new GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtensionSettings();
excelSetting.FileFormat = GrapeCity.ActiveReports.Export.Excel.Page.FileFormat.Xls;
GrapeCity.ActiveReports.Extensibility.Rendering.ISettings setting = excelSetting;

//Set the rendering extension and render the report.
GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtension excelRenderingExtension = new GrapeCity.ActiveReports.Export.Excel.Page.ExcelRenderingExtension();
GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider outputProvider = new GrapeCity.ActiveReports.Rendering.IO.FileStreamProvider(outputDirectory, System.IO.Path.GetFileNameWithoutExtension(outputDirectory.Name));
reportDocument.Render(excelRenderingExtension, outputProvider, excelSetting.GetSettings());

Excel Rendering Properties

ActiveReports offers several options to control how reports render to Microsoft Excel.

Property	Description
PageSettings	Returns an ExcelRenderingExtensionPageSettings object for initializing Excel file print setting.
Pagination	Forces pagination or galley report layout mode.
Security	Returns an ExcelRenderingExtensionSecurity object for initializing document security.
UseDefaultPalette	Indicates whether to export the document with the default Excel palette.
FileFormat	Specifies the output format of the Excel document, i.e. Xls or Xlsx.
OpenXmlStandard	Specifies the level of Open XML document conformance on exporting in Xlsx file format. You can choose between Strict and Transitional value.
MultiSheet	Indicates whether to generate a single-sheet or multi-sheet Excel document.

Interactivity

Reports rendered in Excel support a number of interactive features like Bookmarks and Hyperlinks. However, in case you have any data hidden at the time of rendering (like in a drill-down report), it does not show up in the output. It is recommended that you expand all toggle items prior to rendering.

Limitations

BackgroundImage, overlapping controls, rounded corners (for Shape and Container) are not exported.
LineSpacing is not retained after export.
Exported FormattedText control does not preserve styles and formatting. It exports FormattedText as TextBox with plain text without any tags.
Barcodes are exported as an image object so scanning of barcode image may fail in some cases. It depends on printer settings and the scanner quality. Barcodes may be blurred on export and the caption may get truncated in case of physical printing.
Exported boolean values are displayed in uppercase in both Xls and Xlsx files.
TextIndent property and FillCharacter property of the TableOfContents control's Level setting are not supported.
Text decoration (Underline and LineThrough) gets applied to the indented area, when left padding is applied to the TableOfControl's levels.

Concepts

Exporting